#数据库有关操作
import pymysql
from string import Template
import pandas as pd
#创建数据库连接
connect = pymysql.connect(host='81.70.27.234',port=3306,user='root',password='010825lwj',db='filmInfo',connect_timeout=28800,max_allowed_packet=32)
#创建游标
cursor = connect.cursor()
#获取表中国家不为空的全部数据
def req_country(year:int):
    req_sql = Template("""
                       select * from ${year}_detail where countries is not null
                       """)
    res = pd.read_sql(req_sql.substitute(year = str(year)),connect)
    return res
#将每个国家或地区出现的次数存入表中
def creat_ratio(year:int):
    #判断是否已有此表
    drop_sql = Template("DROP TABLE IF EXISTS ${year}_ratio")
    #创建表
    creat_sql = Template("""
                         create table ${year}_ratio(
                             name varchar(50) not null,
                             value int
                         )
                         """)
    cursor.execute(drop_sql.substitute(year = str(year)))
    cursor.execute(creat_sql.substitute(year = str(year)))
#将每个国家出现的次数加入数据库
def add_count(country:str,count:int,year:int):
    
    add_sql = Template("""
                       insert into ${year}_ratio(name,value) values ('${country}',${count})
                       """)
    try:
        cursor.execute(add_sql.substitute(year = year,country = country,count = count))
        connect.commit()
    except Exception as e:
        print(e)
        print("数据加入数据库出错",country)